package com.zhang.admin.service;

import com.zhang.admin.bean.User;

import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Locale;

/**
 * @title:
 * @author: zhangyf
 * @date: 2023/3/15 15:36
 **/
public class UserService {
    /**
     * 获取所有用户信息
     * @return 用户列表
     */
    public static List<User> getAllUsers(){
        try {
            Class.forName(DATABASE.DRIVER);
            var connection = DriverManager.getConnection(
                    DATABASE.URL,
                    DATABASE.USERNAME,
                    DATABASE.PASSWORD
            );
            var selectStatement = connection.prepareStatement(
                    "select id,username,password from user"
            );
            var resultSet = selectStatement.executeQuery();
            var users = new ArrayList<User>();
            while (resultSet.next()){
                var user = new User();
                user.setId(resultSet.getLong("id"));
                user.setUsername(resultSet.getString("username"));
                user.setPassword(resultSet.getString("password"));
                user.setRoleName(RoleService.getUserRoleName(resultSet.getLong("id")));
                users.add(user);
            }

            selectStatement.close();
            connection.close();
            return users;
        }catch (SQLException | ClassNotFoundException e){
            e.printStackTrace();
        }
        return new ArrayList<>();
    }

    /**
     * 根据用户名查询用户信息
     * @param username
     * @return
     */
    public static User getUserByName(String username){
        try {
            Class.forName(DATABASE.DRIVER);
            var connection = DriverManager.getConnection(
                    DATABASE.URL,
                    DATABASE.USERNAME,
                    DATABASE.PASSWORD
            );

            var selectStatement = connection.prepareStatement(
                    "select id,username,password from user where username = ?"
            );
            selectStatement.setString(1,username);
            var resultSet = selectStatement.executeQuery();
            var user = new User();
            if (resultSet.next()){
                user.setId(resultSet.getLong("id"));
                user.setUsername(resultSet.getString("username"));
                user.setPassword(resultSet.getString("password"));
                user.setRoleName(RoleService.getUserRoleName(resultSet.getLong("id")));
            }
            selectStatement.close();
            connection.close();
            return user;

        } catch (SQLException | ClassNotFoundException e){
            e.printStackTrace();
        }

        return null;
    }

    /**
     * 根据用户id查询用户信息
     * @param id
     * @return
     */
    public static User getUserById(Long userId){
        try {
            Class.forName(DATABASE.DRIVER);
            var connection = DriverManager.getConnection(
                    DATABASE.URL,
                    DATABASE.USERNAME,
                    DATABASE.PASSWORD
            );
            var selectStatement = connection.prepareStatement(
                    "select id,username,password from user where id = ?"
            );
            selectStatement.setLong(1,userId);
            var resultSet = selectStatement.executeQuery();
            var user = new User();
            if (resultSet.next()){
                user.setId(resultSet.getLong("id"));
                user.setUsername(resultSet.getString("username"));
                user.setPassword(resultSet.getString("password"));
                user.setRoleName(RoleService.getUserRoleName(resultSet.getLong("id")));
            }
            selectStatement.close();
            connection.close();
            return user;

        }catch (SQLException | ClassNotFoundException e){
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 根据用户id删除用户，在用户-角色表中需要将对应的用户id的用户角色数据删除
     * @param userId
     */
    public static void deleteUserById(Long userId){
        try {
            Class.forName(DATABASE.DRIVER);
            var connection = DriverManager.getConnection(
                    DATABASE.URL,
                    DATABASE.USERNAME,
                    DATABASE.PASSWORD
            );
            // 删除用户数据
            var deleteUser = connection.prepareStatement(
                    "delete from user where id = ? "
            );
            deleteUser.setLong(1,userId);
            deleteUser.execute();

            //删除用户对应的角色信息
            var deleteUserRole = connection.prepareStatement(
                    "delete from user_role where user_id = ? "
            );
            deleteUserRole.setLong(1, userId);
            deleteUserRole.execute();

            deleteUser.close();
            deleteUserRole.close();
            connection.close();

        }catch (SQLException | ClassNotFoundException e){
            e.printStackTrace();
        }
    }

    /**
     * 添加新用户
     * @param username
     * @param password
     */
    public static void addUser(String username,String password){
        try {
            Class.forName(DATABASE.DRIVER);
            var connection = DriverManager.getConnection(
                    DATABASE.URL,
                    DATABASE.USERNAME,
                    DATABASE.PASSWORD
            );
            var insertStatement = connection.prepareStatement(
                    "insert into user (username,password) values (?,?) "
            );
            insertStatement.setString(1,username);
            insertStatement.setString(2,password);
            insertStatement.execute();
            insertStatement.close();
            connection.close();
        }catch (SQLException | ClassNotFoundException e){
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        System.out.println(UserService.getAllUsers());
        System.out.println(UserService.getUserByName("lisi"));
        System.out.println(UserService.getUserById(1L));
    }
}
